#操作mysql常见第三方库
#mysql-connector
#pymysql

# mysql-connector安装
import mysql.connector

"""
mysql.connector基本应用
"""
#数据库：存储项目数据
def createDatabase():
    """
    创建连接对象
    新建数据库
    :param database:
    :return:
    """
    conn = mysql.connector.connect(
        host="localhost",
        user="root",
        password="lbf4hzynb",
       )
    print(conn)
    cursor = conn.cursor() #获取游标
    sql="create database mashang_211"
    cursor.execute(sql)    #参数为sql语句
    cursor.execute("show databases")    #显示数据库内容
    for database in cursor:
        print(database)


def connect(database):
    """
    #   1、连接指定数据库
    :param database: 数据库名
    :return:
    """
    conn = mysql.connector.connect(
        host="localhost",
        user="root",
        password="lbf4hzynb",
        database=database   #数据库名
       )
    print(conn)

def createsql():
    """
    创建表

    :return:
    """
    conn = mysql.connector.connect(
        host="localhost",
        user="root",
        password="lbf4hzynb",
        database = "mashang_211"
    )
    cursor = conn.cursor()  # 获取游标
    sql = "create table user(id int auto_increment primary key,name varchar(20),calssname varchar(20))"
    cursor.execute(sql)

def insertsql():
    """
    往表中插入数据
    :return:
    """
    conn = mysql.connector.connect(
        host="localhost",
        user="root",
        password="lbf4hzynb",
        database="mashang_211"
    )
    cursor = conn.cursor()  # 获取游标
    sql = "insert into user(name,calssname) values(%s,%s)"
    svalue=("dfrg001","m21001")
    cursor.execute(sql,svalue)
    conn.commit()   #提交sql并执行
    rowcount =cursor.rowcount   #返回sql执行情况说明
    print(f"{rowcount}行记录插入成功！")
    # 执行查询时，若游标未重置，则游标的位置会在一直向下移动
    cursor.execute("select * from user")
    # res = cursor.fetchall() #获取所有数据
    # res_01 = cursor.fetchone()  #获取第一条数据
    res_many = cursor.fetchmany(3)  #获取前三条数据
    # print(res)
    # print(res_01)
    print(res_many)

def lotInsert():
    """
    批量插入数据
    :return:
    https://blog.csdn.net/figerdeng/article/details/50493483
    """
    pass

if __name__=="__main__":

    insertsql()










